package excelintegration;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Locale;

import javax.swing.JFrame;
import javax.swing.WindowConstants;

import jxl.Cell;
import jxl.DateCell;
import jxl.HeaderFooter.Contents;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.NumberFormulaCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

public class ExcelImporter {
	public static final String DRIVER_NAME = "sun.jbdc.odbc.JdbcOdbcDriver";
	public static final String DATABASE_URL = "jdbc:odbc:.//DB/PROSPETTO_CALDAIE_2010_xls";
	
	private static final String[] MONTHS = {"Gennaio", "Febbraio", "Marzo", "Aprile", "Maggio", "Giugno", "Luglio", "Agosto", "Settembre", "Ottobre", "Novembre", "Dicembre"};
	
	
	public static void main(String[] args)
	{
		ExcelContent excCont = new ExcelContent();
		try {
			String fileName = ".//DB/PROSPETTO_CALDAIE_2010.xls";
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("it", "IT"));
			
			Workbook workbook = Workbook.getWorkbook(new File(fileName), ws);
			Sheet s = workbook.getSheet(0);
			excCont.setContentSheetName1(s.getName());
			readDataSheet(s, excCont);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	private static void readDataSheet(Sheet s, ExcelContent excelContent)
	  {
	    // Find the labeled cell from sheet
	    LabelCell lc = s.findLabelCell("De Cesare Tullio*");
	    System.out.println(lc.getString());
	    System.out.println("column: " + lc.getColumn() + " - row: " + lc.getRow());
	    String tempLine = "";
	    for(int i=3; i<s.getRows(); i++)
	    {
	    	Cell c = s.getCell(2, i);
	    	if(!c.getContents().equalsIgnoreCase("") && !c.getContents().equalsIgnoreCase(" ") && !containsMonthsName(c.getContents()))
	    	{
	    		System.out.println(c.getContents());
	    		// Ora si dovrebbe scorrere le colonne e prenderne il contenuto
	    		tempLine = "";
	    		for(int j=2; j<s.getColumns(); j++)
	    		{
	    			c = s.getCell(j, i);
	    			tempLine += c.getContents() + " - ";
	    		}
	    		if(!tempLine.equals(""))
	    		{
	    			System.out.println(tempLine);
	    			excelContent.addLineToContentSheet1(tempLine);
	    		}
	    		
	    	}	    	
	    }
	    System.out.println("Totale Numero linee (" + excelContent.getContentSheet1Name() + "): " + excelContent.getNumberOfLineInContentSheet1());
	    JFrame frame = new JFrame();
	    System.out.println(excelContent.getLineAt(0));
		frame.getContentPane().add(new RiepilogoDatiInserimentoJPanel(excelContent.getLineAt(0), excelContent.getContentSheet1Name()));
		frame.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
		frame.pack();
		frame.setVisible(true);
	    //gets the value of cell at specified column and row
	    /*
	    DateCell dc =(DateCell) s.getCell(0,1);
	    System.out.println(dc.getDate());

	    lc = s.findLabelCell("Add 2 cells");
	    System.out.println(lc.getString());

	    NumberCell c =(NumberCell) s.getCell(4,1);
	    System.out.println(c.getValue());

	    c =(NumberCell) s.getCell(4,2);
	    System.out.println(c.getValue());

	    NumberFormulaCell nc =(NumberFormulaCell) s.getCell(4,3);
	    System.out.println(nc.getValue());
		*/
	  }
	
	private static boolean containsMonthsName(String cellContent)
	{
		for(int i=0; i<MONTHS.length; i++)
			if(cellContent.equalsIgnoreCase(MONTHS[i]))
				return true;
		return false;
	}

}
